using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.IO;

using IDAL;
using Entities;

namespace OleDBDAL
{
    public class CategoryDAO : ICategoryDAO
    {
        private const string SQL_SELECT_CATEGORIES = "select * from Categories";
        private const string SQL_SELECT_IDENTITY_PARM = "select @@IDENTITY";
        private const string SQL_INSERT_CATEGORY = "insert into Categories(CategoryName, Description, Picture) values(@CategoryName, @Description, @Picture)";

        private const string SQL_UPDATE_CATEGORY = "update Categories set CategoryName = @CategoryName, Description = @Description, Picture = @Picture where CategoryID = @CategoryID";

        private const string SQL_DELETE_CATEGORY = "delete from Categories where CategoryID = @CategoryID";

        // Parameters
        private const string PARM_CATEGORY_ID = "@CategoryID";
        private const string PARM_CATEGORY_NAME = "@CategoryName";
        private const string PARM_CATEGORY_DESCRIPTION = "@Description";
        private const string PARM_CATEGORY_PICTURE = "@Picture";

        private byte[] GetBytesFromImage(Image img)
        {
            // chuyen doi tuong Image luu vao mang bytes
            byte[] bytes;
            MemoryStream ms = new MemoryStream();

            img.Save(ms, img.RawFormat);

            bytes = ms.GetBuffer();

            return bytes;
        }
        
        #region ICategoryDAL Members

        public IList<CategoryInfo> GetAll()
        {
            IList<CategoryInfo> categories = new List<CategoryInfo>();
            using (OleDbDataReader reader = OleDBHelper.ExecuteReader(OleDBHelper.CONN_STRING,
                CommandType.Text, SQL_SELECT_CATEGORIES, null))
            {
                while (reader.Read())
                {
                    CategoryInfo cate = new CategoryInfo();
                    cate.CategoryID = int.Parse(reader.GetValue(0).ToString());
                    cate.CategoryName = reader.GetString(1);
                    if (!reader.IsDBNull(2))
                        cate.Description = reader.GetString(2);
                    if (!reader.IsDBNull(3))
                    {
                        byte[] bytes = (byte[])reader.GetValue(3);
                        ImageConverter imgConv = new ImageConverter();
                        cate.Picture = ((Image)(imgConv.ConvertFrom(bytes)));
                    }
                    categories.Add(cate);
                }
            }
            return categories;
        }

        public bool Insert(CategoryInfo cate)
        {
            OleDbParameter[] parms;
            parms = new OleDbParameter[] {
											 new OleDbParameter(PARM_CATEGORY_NAME, OleDbType.WChar),
											 new OleDbParameter(PARM_CATEGORY_DESCRIPTION, OleDbType.LongVarWChar),
                                             new OleDbParameter(PARM_CATEGORY_PICTURE, OleDbType.Binary)};
            parms[0].Value = cate.CategoryName;
            parms[1].Value = cate.Description;
            parms[2].Value = GetBytesFromImage(cate.Picture);

            try
            {
                int result = 0;
                OleDbConnection conn = new OleDbConnection(OleDBHelper.CONN_STRING);
                result = OleDBHelper.ExecuteNonQuery(conn,
                    CommandType.Text, SQL_INSERT_CATEGORY, parms);
                // get the CategoryID, CategoryID is an Auto number column
                object obj = OleDBHelper.ExecuteScalar(conn,
                    CommandType.Text, SQL_SELECT_IDENTITY_PARM, null);
                if (obj != null)
                    cate.CategoryID = int.Parse(obj.ToString());
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                return (result > 0 ? true : false);
            }
            catch (Exception e)
            {
                throw e;
            }
            return false;
        }

        public bool Update(CategoryInfo cate)
        {
            OleDbParameter[] parms;
            parms = new OleDbParameter[] {
											 new OleDbParameter(PARM_CATEGORY_NAME, OleDbType.WChar),
											 new OleDbParameter(PARM_CATEGORY_DESCRIPTION, OleDbType.LongVarWChar),
                                             new OleDbParameter(PARM_CATEGORY_PICTURE, OleDbType.Binary),
                                             new OleDbParameter(PARM_CATEGORY_ID, OleDbType.Integer)};
            parms[0].Value = cate.CategoryName;
            parms[1].Value = cate.Description;
            parms[2].Value = GetBytesFromImage(cate.Picture);
            parms[3].Value = cate.CategoryID;

            try
            {
                int result = 0;
                result = OleDBHelper.ExecuteNonQuery(OleDBHelper.CONN_STRING,
                    CommandType.Text, SQL_UPDATE_CATEGORY, parms);
                return (result > 0 ? true : false);
            }
            catch (Exception e)
            {
                throw e;
            }
            return false;
        }

        public bool Delete(CategoryInfo cate)
        {
            OleDbParameter[] parms;
            parms = new OleDbParameter[] { new OleDbParameter(PARM_CATEGORY_ID, OleDbType.Integer) };
            parms[0].Value = cate.CategoryID;

            try
            {
                int result = 0;
                result = OleDBHelper.ExecuteNonQuery(OleDBHelper.CONN_STRING,
                    CommandType.Text, SQL_DELETE_CATEGORY, parms);
                return (result > 0 ? true : false);
            }
            catch (Exception e)
            {
                throw e;
            }
            return false;
        }

        #endregion
    }
}
